package nl.mayisal.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Properties;

import nl.mayisal.metadata.DatabaseStructure;
import nl.mayisal.metadata.TableRelation;
import nl.mayisal.metadata.TableStructure;
import nl.mayisal.metadata.columns.AbstractColumnStructure;
import nl.mayisal.metadata.factories.ColumnStructureFactory;

public class SQLConnection {
	final Connection connection;
	private boolean connectionIsOpen;
	
	public SQLConnection(String url, Properties info) throws SQLException {
		this.connection = DriverManager.getConnection(url, info);
		this.connectionIsOpen = true;
	}
	
	public String getCatalogName() {
		try {
			return this.connection.getCatalog();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public String getDriverDescription() {
		DatabaseMetaData metaData;
		try {
			metaData = this.connection.getMetaData();
			return metaData.getDriverName() + " " + metaData.getDriverVersion() + " ("+metaData.getDriverMajorVersion()+"."+metaData.getDriverMinorVersion()+")\n";
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public String getDatabaseDescription() {
		DatabaseMetaData metaData;
		try {
			metaData = this.connection.getMetaData();
			return metaData.getDatabaseProductName() + " " + metaData.getDatabaseProductVersion() + " ("+ metaData.getDatabaseMajorVersion() +"."+metaData.getDatabaseMinorVersion()+")\n";
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Collection<String> getAvailableTableTypes() {
		ArrayList<String> tableTypes = new ArrayList<String>();
		try {
			ResultSet rs = this.connection.getMetaData().getTableTypes();
			while (rs.next()) {
				tableTypes.add(rs.getString(1));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tableTypes;
	}

	public Collection<String> getTableNames(String tableType) {
		ArrayList<String> tableNames = new ArrayList<String>();
		try {
			ResultSet rs = this.connection.getMetaData().getTables(this.getCatalogName(), null,null, new String[] { tableType });
			while (rs.next()) {
				String schema = rs.getString(2);
				tableNames.add((schema == null?"":schema+".") + rs.getString(3));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tableNames;
	}
	
	public Collection<AbstractColumnStructure<?>> getColumns(String table) {
		ArrayList<AbstractColumnStructure<?>> columnNames = new ArrayList<AbstractColumnStructure<?>>();
		try {
			ResultSet rs = this.connection.getMetaData().getColumns(this.getCatalogName(), null, table, null);
			while (rs.next()) {
				AbstractColumnStructure<?> column = 
					ColumnStructureFactory.createColumn(
						rs.getString(4),	// COLUMN_NAME String => column name 
						rs.getInt(5),		// DATA_TYPE int => SQL type from java.sql.Types 
						rs.getInt(7),		// COLUMN_SIZE int => column size. 
						rs.getInt(9),		// DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
						rs.getInt(10),		// NUM_PREC_RADIX int => Radix (typically either 10 or 2)
						rs.getString(13)	// COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null) 
					);
				if (rs.getInt(11) == 1) {
					column.setNullable(true);
				}
				columnNames.add(column);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return columnNames;
	}

	public Collection<TableRelation> getRelations(DatabaseStructure database, TableStructure table) {
		ArrayList<TableRelation> relations = new ArrayList<TableRelation>();
		try {
			ResultSet rs = this.connection.getMetaData().getCrossReference(getCatalogName(), null, table.getName(), null, null, null);
			while (rs.next()) {
				AbstractColumnStructure<?> parentColumn = table.getColumn(rs.getString(4));
				TableStructure foreignTable = database.getTable(rs.getString(7));
				AbstractColumnStructure<?> foreignColumn = foreignTable.getColumn(rs.getString(8));

				relations.add(new TableRelation(table,parentColumn,foreignTable,foreignColumn));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return relations;
	}

	public void close() {
		this.connectionIsOpen = false;
		try {
			this.connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	
	@Override
	protected void finalize() throws Throwable {
		if (this.connectionIsOpen)
			this.close();
		super.finalize();
	}

	public SQLResultSet query(String query) {
		SQLResultSet resultset = null;
		try {
			Statement statement = this.connection.createStatement();
			resultset = new SQLResultSet(statement, statement.executeQuery(query));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultset;
	}
}
